import pandas as pd
import itertools

# 读取原始Excel文件
df = pd.read_excel('/Users/Cench/Desktop/HDxVTxCL_1.xlsx')

# 获取HD、VT和CL的所有可能组合
hd_categories = [1, 2, 3]
vt_categories = [1, 2, 3]
cl_categories = [1, 2]
combinations = list(itertools.product(hd_categories, vt_categories, cl_categories))

# 创建新的DataFrame用于存储结果
result_df = pd.DataFrame(columns=['HDxVTxCL', 'Transmission_Ln'])

# 遍历每个组合，并获取相应的Transmission_Ln值
for combination in combinations:
    hd, vt, cl = combination
    combination_name = f'HD{hd}xVT{vt}xCL{cl}'
    filtered_data = df[(df['HD'] == hd) & (df['VT'] == vt) & (df['CL'] == cl)]
    transmission_ln_values = filtered_data['Transmission_Ln'].tolist()
    for transmission_ln in transmission_ln_values:
        result_df = result_df.append({'HDxVTxCL': combination_name, 'Transmission_Ln': transmission_ln}, ignore_index=True)

# 保存结果到新的Excel文件
result_df.to_excel('/Users/Cench/Desktop/HDxVTxCL_2.xlsx', index=False)
